package com.eascs.admin.o2o.backfee.service;

import java.math.BigDecimal;
import java.util.GregorianCalendar;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.alibaba.dubbo.config.annotation.Reference;
import com.alibaba.fastjson.JSONObject;
import com.eascs.admin.common.service.BaseSevice;
import com.eascs.admin.o2o.backfee.dao.O2OComBackFeeDao;
import com.eascs.admin.o2o.backfee.dao.OaComBackFeeDao;
import com.eascs.admin.o2o.backfee.entity.FormBackFeeEntity;
import com.eascs.admin.o2o.backfee.entity.O2OComBackFee;
import com.eascs.admin.o2o.backfee.entity.O2OComReceiveFee;
import com.eascs.admin.o2o.backfee.entity.OaBackFeeEntity;
import com.eascs.admin.o2o.backfee.entity.OaBackFeeStatus;
import com.eascs.admin.o2o.backfee.entity.OaUpdateBackFeeEntity;
import com.eascs.admin.o2o.detail.dao.OaDetailDao;
import com.eascs.admin.o2o.internal.entity.LoanapplyExpand;
import com.eascs.admin.o2o.internal.service.LoanapplyExpandService;
import com.eascs.admin.o2o.internal.util.BaseUserUtil;
import com.eascs.admin.o2o.internal.util.BaseUtils;
import com.eascs.admin.o2o.internal.util.StatusContexts;
import com.eascs.admin.o2o.loan.entity.O2oLoanapplyEntity;
import com.eascs.admin.o2o.loan.service.O2oLoanapplyService;
import com.eascs.admin.oldO2olike.form.OldO2oLikeForm;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.DateUtils;
import com.eascs.loan.service.O2oLoanService;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;
import com.eascs.workflow.common.entity.wf.ProcessTemplate;
import com.eascs.workflow.common.service.wf.WorkFlowService;
import com.eascs.workflow.common.vo.ModelResult;
import com.eascs.workflow.common.vo.WFRequestParam;

import jodd.util.StringUtil;

@Service
public class O2OComBankFeeService extends BaseSevice {
	private final Logger logger = LoggerFactory.getLogger(O2OComBankFeeService.class);
	@Autowired
	private O2OComBackFeeDao o2OComBackFeeDao;
	@Autowired
	OaComBackFeeDao oaComBackFeeDao;
	@Autowired
	BaseUserUtil userUtils;
	@Reference
	private WorkFlowService workFlowService;
	@Autowired
	O2oLoanapplyService o2oLoanapplyService;
	@Autowired
	O2oLoanService      o2oLoanService;
	@Autowired
	OaDetailDao         oaDetailDao;

	
	@Value("${o2o.admin.backfeeUrl}")
	private String backfeeUrl;

	@Autowired
	private LoanapplyExpandService loanapplyExpandService;

	public PageResult<Object[]> findByPage(FormBackFeeEntity backFeeEntity) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, backFeeEntity.getPageSize(), backFeeEntity.getShowO2oPage());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select l.bill_code,ou.user_full_name,(case l.credit_status when 0 then 'ZXSX' else l.wf_type end) as 'wf_type', ");
		querySql.append("IFNULL(ole.red_deposite,0.0000),IFNULL(ole.reta_deposite,0.0000),");
		querySql.append("IFNULL(ole.red_deposite,0.0000) - IFNULL(ole.reta_deposite,0.0000),");
		querySql.append("IFNULL(ole.red_manger,0.0000),IFNULL(ole.reta_manage,0.0000),");
		querySql.append("IFNULL(ole.red_manger,0.0000) - IFNULL(ole.reta_manage,0.0000),");
		querySql.append("IFNULL(ole.red_risk,0.0000),IFNULL(ole.reta_risk,0.0000),");
		querySql.append("IFNULL(ole.red_risk,0.0000) - IFNULL(ole.reta_risk,0.0000),ole.pay_date,");
		if(StringUtils.isEmpty(backFeeEntity.getFlag())){
			querySql.append(" ole.bill_status   ");
		}else {
			querySql.append("'已缴纳' as bill_status");
		}
		querySql.append(" from o2o_loanapply_expand ole  ");
		querySql.append(" LEFT JOIN o2o_loanapply l on l.loanapply_no = ole.loanapply_no  ");
		querySql.append(" LEFT JOIN o2o_loan_user ou on ou.loanapply_no = ole.loanapply_no ");
		//querySql.append(" and  ou.loan_id = l.loan_id where /**DATA_PERMISSION(l.ltd)**/ 1=1 and ole.bill_status = 'CLO' ");

		querySql.append(" and  ou.loan_id = l.loan_id where  ");
		querySql.append(" 1=1 and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(backFeeEntity.getXdjlNo())) {
			querySql.append(" or l.operator_no in ("+backFeeEntity.getXdjlNo()+"))");
		}else {
			querySql.append(" or /**DATA_PERMISSION(l.ltd)**/ 1=1 ) ");
		}
		
		querySql.append("  and ole.bill_status = 'CLO' ");
		
		if (!StringUtil.isEmpty(backFeeEntity.getBill_code())) {
			querySql.append(" AND l.bill_code LIKE ? ");
			pageQuery.addLikeParam(backFeeEntity.getBill_code());
			logger.debug("oa params bill_code={}", backFeeEntity.getBill_code());
		}
		if (!StringUtil.isEmpty(backFeeEntity.getName())) {
			querySql.append(" AND ou.user_full_name LIKE ? ");
			pageQuery.addLikeParam(backFeeEntity.getName());
		}
		
		if(!StringUtil.isEmpty(backFeeEntity.getCustomerId())&&StringUtil.isEmpty(backFeeEntity.getSpouseId())){
			querySql.append(" AND  ou.loan_id = ? ");
			pageQuery.addParam(backFeeEntity.getCustomerId());
		}
		
		if (!StringUtil.isEmpty(backFeeEntity.getPayMentStatus())) {
			if ("O".equals(backFeeEntity.getPayMentStatus())) {
				querySql.append(" and wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(backFeeEntity.getPayMentStatus())){
				querySql.append(" and wf_type=0");
			}else {
				querySql.append(" and wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (backFeeEntity.getPayMentStatus().contains(",")) {
					wfTypeA = backFeeEntity.getPayMentStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + backFeeEntity.getPayMentStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				querySql.append(wfType);
				querySql.append(" ) ");
			}
		}
		
		if(!StringUtil.isEmpty(backFeeEntity.getCustomerId())&&!StringUtil.isEmpty(backFeeEntity.getSpouseId())){
			querySql.append(" AND ( ou.loan_id = ? or ou.loan_id = ? )");
			pageQuery.addParam(backFeeEntity.getCustomerId());
			pageQuery.addParam(backFeeEntity.getSpouseId());
		}
		
		querySql.append(" ORDER BY l.bill_code DESC ");
		pageQuery.setQuerySql(querySql.toString());
		return o2OComBackFeeDao.findDataWithPage(pageQuery);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               保证金与管理费 报表导出
	 *               </p>
	 * @author LMX
	 * @date 2018年1月30日 上午11:00:36
	 * @version V1.0
	 * @param backFeeEntity
	 * @return
	 */
	public PageResult<Object[]> findBzjglfExport(FormBackFeeEntity backFeeEntity) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, backFeeEntity.getPageSize(), backFeeEntity.getShowO2oPage());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select l.bill_code,ou.user_full_name, ");
		querySql.append("(SELECT pdo.item_name FROM pub_dditem_o2o pdo LEFT JOIN pub_datadictionary_o2o d ON pdo.dd_no=d.dd_no WHERE d.dd_code = 'SQZT' AND pdo.item_code= l.wf_type) as wf_type, ");
		querySql.append("IFNULL(ole.red_deposite,0.0000),IFNULL(ole.reta_deposite,0.0000),");
		querySql.append("IFNULL(ole.red_deposite,0.0000) - IFNULL(ole.reta_deposite,0.0000),");
		querySql.append("IFNULL(ole.red_manger,0.0000),IFNULL(ole.reta_manage,0.0000),");
		querySql.append("IFNULL(ole.red_manger,0.0000) - IFNULL(ole.reta_manage,0.0000),");
		querySql.append("IFNULL(ole.red_risk,0.0000),IFNULL(ole.reta_risk,0.0000),");
		querySql.append("IFNULL(ole.red_risk,0.0000) - IFNULL(ole.reta_risk,0.0000),ole.pay_date,");
		if(StringUtils.isEmpty(backFeeEntity.getFlag())){
			querySql.append(" ole.bill_status   ");
		}else {
			querySql.append("'已缴纳' as bill_status");
		}
		querySql.append(" from o2o_loanapply_expand ole  ");
		querySql.append(" LEFT JOIN o2o_loanapply l on l.loanapply_no = ole.loanapply_no  ");
		querySql.append(" LEFT JOIN o2o_loan_user ou on ou.loanapply_no = ole.loanapply_no ");
		//querySql.append(" and  ou.loan_id = l.loan_id where /**DATA_PERMISSION(l.ltd)**/ 1=1 and ole.bill_status = 'CLO' ");
		
		querySql.append(" and  ou.loan_id = l.loan_id where  ");
		querySql.append(" 1=1 and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(backFeeEntity.getXdjlNo())) {
			querySql.append(" or l.operator_no in ("+backFeeEntity.getXdjlNo()+"))");
		}else {
			querySql.append(" or /**DATA_PERMISSION(l.ltd)**/ 1=1 ) ");
		}
		
		querySql.append("  and ole.bill_status = 'CLO' ");
		
		if (!StringUtil.isEmpty(backFeeEntity.getBill_code())) {
			querySql.append(" AND l.bill_code LIKE ? ");
			pageQuery.addLikeParam(backFeeEntity.getBill_code());
			logger.debug("oa params bill_code={}", backFeeEntity.getBill_code());
		}
		if (!StringUtil.isEmpty(backFeeEntity.getName())) {
			querySql.append(" AND ou.user_full_name LIKE ? ");
			pageQuery.addLikeParam(backFeeEntity.getName());
		}
		
		if(!StringUtil.isEmpty(backFeeEntity.getCustomerId())&&StringUtil.isEmpty(backFeeEntity.getSpouseId())){
			querySql.append(" AND  ou.loan_id = ? ");
			pageQuery.addParam(backFeeEntity.getCustomerId());
		}
		
		if (!StringUtil.isEmpty(backFeeEntity.getPayMentStatus())) {
			if ("O".equals(backFeeEntity.getPayMentStatus())) {
				querySql.append(" and wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(backFeeEntity.getPayMentStatus())){
				querySql.append(" and wf_type=0");
			}else {
				querySql.append(" and wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (backFeeEntity.getPayMentStatus().contains(",")) {
					wfTypeA = backFeeEntity.getPayMentStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + backFeeEntity.getPayMentStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				querySql.append(wfType);
				querySql.append(" ) ");
			}
		}
		
		if(!StringUtil.isEmpty(backFeeEntity.getCustomerId())&&!StringUtil.isEmpty(backFeeEntity.getSpouseId())){
			querySql.append(" AND ( ou.loan_id = ? or ou.loan_id = ? )");
			pageQuery.addParam(backFeeEntity.getCustomerId());
			pageQuery.addParam(backFeeEntity.getSpouseId());
		}
		
		querySql.append(" ORDER BY l.bill_code DESC ");
		pageQuery.setQuerySql(querySql.toString());
		return o2OComBackFeeDao.findDataWithPage(pageQuery);
	}
	
	/**
	 * @Title: findSfbzjByPage
	 * @Description: 收费保证金和管理费
	 * @author 张庆奇
	 * @param backFeeEntity
	 * @return
	 * @return PageResult<Object[]>
	 * @throws
	 * @date 2017年7月20日 下午4:12:36
	 */
	public PageResult<Object[]> findSfbzjByPage(OldO2oLikeForm oldO2oLikeForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, oldO2oLikeForm.getPageSize(), oldO2oLikeForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : oldO2oLikeForm.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		if(StringUtil.isEmpty(oldO2oLikeForm.getFlag())){
			querySql.append("select l.bill_code,ou.user_full_name,l.loan_type,l.ltd_name,l.operator_name,");
			querySql.append(" round(l.ea_mount/10000 ,2) ea_mount, l.bank_loan_name,l.wf_type,ole.bill_status,ole.creat_time" );
			querySql.append(" from o2o_loanapply_expand ole  ");
			querySql.append(" LEFT JOIN o2o_loanapply l on l.loanapply_no = ole.loanapply_no  ");
			querySql.append(" LEFT JOIN o2o_loan_user ou on ou.loanapply_no = ole.loanapply_no ");
			//querySql.append(" and  ou.loan_id = l.loan_id where /**DATA_PERMISSION(l.ltd)**/ ");
			querySql.append(" and  ou.loan_id = l.loan_id where  ");

			querySql.append(" 1=1 and (l.creat_ro="+UserHolder.getUser().getNo());
			if (!StringUtils.isEmpty(oldO2oLikeForm.getXdjlNo())) {
				querySql.append(" or l.operator_no in ("+oldO2oLikeForm.getXdjlNo()+"))");
			}else {
				querySql.append(" or /**DATA_PERMISSION(l.ltd)**/ 1=1 ) ");
			}
			
			querySql.append(" and l.wf_type in('NF','S','SF','O','SN','T','HN','DC','YQ')");
			}else{
				querySql.append(" select l.bill_code,ou.user_full_name,case when l.loan_type='Bk' then '终端贷'");
				querySql.append(" when l.loan_type='EA' then '接力贷' when l.loan_type='FQ' then '分期贷' ");
				querySql.append(" when l.loan_type='BP' then '品牌贷-厂家' when l.loan_type='BP_JX' then '品牌贷-经销商' ");
				querySql.append(" when l.loan_type='SG' then '蔬果贷' when l.loan_type='YC' then '烟草贷' ");
				querySql.append(" when l.loan_type='SC' then '商超贷' end as loan_type,l.ltd_name,l.operator_name,");
				querySql.append(" l.ea_mount,(select d.item_name from pub_dditem d where d.DD_NO=10340 and d.item_code= l.bank_loan_name )  as bank_loan_name, (select d.item_name from pub_dditem_o2o d where d.DD_NO=2 and d.item_code= l.wf_type ) as wf_type, ");
				querySql.append(" case when  ole.bill_status='NEW' then '新建' when ole.bill_status='RUN' then '审核中' ") ;
				querySql.append(" when ole.bill_status='CLO' then '流程结束' end as bill_status,ole.creat_time ");
				querySql.append(" from o2o_loanapply_expand ole  ");
				querySql.append(" LEFT JOIN o2o_loanapply l on l.loanapply_no = ole.loanapply_no  ");
				querySql.append(" LEFT JOIN o2o_loan_user ou on ou.loanapply_no = ole.loanapply_no ");
				//querySql.append(" and  ou.loan_id = l.loan_id where /**DATA_PERMISSION(l.ltd)**/  ");
				
				querySql.append(" and  ou.loan_id = l.loan_id where  ");
				querySql.append(" 1=1 and (l.creat_ro="+UserHolder.getUser().getNo());
				if (!StringUtils.isEmpty(oldO2oLikeForm.getXdjlNo())) {
					querySql.append(" or l.operator_no in ("+oldO2oLikeForm.getXdjlNo()+"))");
				}else {
					querySql.append(" or /**DATA_PERMISSION(l.ltd)**/ 1=1 ) ");
				}
				
				querySql.append(" and l.wf_type in('NF','S','SF','O','SN','T','HN','DC','YQ')");
				
			}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillCode())) {
			querySql.append(" AND l.bill_code LIKE ? ");
			pageQuery.addLikeParam(oldO2oLikeForm.getBillCode());
			logger.debug("oa params bill_code={}", oldO2oLikeForm.getBillCode());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getUserSurname())) {
			querySql.append(" AND ou.user_full_name LIKE ? ");
			pageQuery.addLikeParam(oldO2oLikeForm.getUserSurname());
		}
		
		if (!StringUtil.isEmpty(oldO2oLikeForm.getSpState())) {
			querySql.append(" AND ole.bill_status = ? ");
			pageQuery.addParam(oldO2oLikeForm.getSpState());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getLoanType())) {
			querySql.append(" and l.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(oldO2oLikeForm.getLoanType().contains(",")){
				loanTypeA = oldO2oLikeForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+oldO2oLikeForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
//		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillStatus())) {
//			querySql.append(" AND l.wf_type = ? ");
//			pageQuery.addParam(oldO2oLikeForm.getBillStatus());
//		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillStatus())) {
			if ("O".equals(oldO2oLikeForm.getBillStatus())) {
				querySql.append(" and l.wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(oldO2oLikeForm.getBillStatus())){
				querySql.append(" and l.wf_type=0");
			}else {
				querySql.append(" and l.wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (oldO2oLikeForm.getBillStatus().contains(",")) {
					wfTypeA = oldO2oLikeForm.getBillStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + oldO2oLikeForm.getBillStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				querySql.append(wfType);
				querySql.append(" ) ");
			}
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getStartTime())) {
			querySql.append(" and ole.creat_time >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStartTime());
		}
		
		if (!StringUtil.isEmpty(oldO2oLikeForm.getEndTime())) {
			querySql.append(" and ole.creat_time <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		
		querySql.append(" ORDER BY ole.creat_time DESC ");
		pageQuery.setQuerySql(querySql.toString());
		return o2OComBackFeeDao.findDataWithPage(pageQuery);
	}
	
	public PageResult<Object[]> findDBByPage(String loanId,String sponsorId) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select l.bill_code,ou.user_full_name, ");
		querySql.append("IFNULL(ole.red_deposite,0.0000),IFNULL(ole.reta_deposite,0.0000),");
		querySql.append("IFNULL(ole.red_deposite,0.0000) - IFNULL(ole.reta_deposite,0.0000)");
		querySql.append(" from o2o_loanapply_expand ole  ");
		querySql.append(" LEFT JOIN o2o_loanapply l on l.loanapply_no = ole.loanapply_no  ");
		querySql.append(" LEFT JOIN o2o_loan_user ou on ou.loanapply_no = ole.loanapply_no ");
		querySql.append(" and  ou.loan_id = l.loan_id where /**DATA_PERMISSION(l.ltd)**/ 1=1 and ole.bill_status = 'CLO' ");
		
		querySql.append(" AND (ou.loan_id = ? or ou.loan_id = ?) ");
		pageQuery.addParam(loanId);
		pageQuery.addParam(sponsorId);
		querySql.append(" ORDER BY l.bill_code DESC ");
		pageQuery.setQuerySql(querySql.toString());
		return o2OComBackFeeDao.findDataWithPage(pageQuery);
	}
	

	public PageResult<Object[]> findByPageList(FormBackFeeEntity backFeeEntity) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, backFeeEntity.getPageSize(), backFeeEntity.getShowO2oPage());
		StringBuffer querySql = new StringBuffer();
		//querySql.append(" select ocb.id,ocb.bill_code,ocb.loaner_name,ocb.bank_name,ocb.bank_addr,ocb.bank_account,ocb.account_name,ocb.back_risk,ocb.back_manger,ocb.back_deposite,ocb.bill_status");
		querySql.append(" select ocb.id,ocb.bill_code,ocb.loaner_name,ocb.bank_name,ocb.bank_account,ocb.account_name,ocb.back_risk,ocb.back_manger,ocb.back_deposite,ocb.bill_status");
		querySql.append(" from o2o_com_backfee ocb  LEFT JOIN o2o_loanapply l on l.loanapply_no = ocb.loanapply_no ");
		//querySql.append(" where ((/**DATA_PERMISSION(l.ltd)**/ 1=1 ) or ocb.bill_code like '%O2O%' )");
		
		querySql.append(" where ");
		
		querySql.append(" ( 1=1 and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(backFeeEntity.getXdjlNo())) {
			querySql.append(" or l.operator_no in ("+backFeeEntity.getXdjlNo()+"))");
		}else {
			querySql.append(" or /**DATA_PERMISSION(l.ltd)**/ 1=1 ) ");
		}
		
		querySql.append("or ocb.bill_code like '%O2O%' )");
		
		if (!StringUtil.isEmpty(backFeeEntity.getBill_code())) {
			querySql.append(" AND ocb.bill_code = ? ");
			pageQuery.addParam(backFeeEntity.getBill_code());
			logger.debug("oa params bill_code={}", backFeeEntity.getBill_code());
		}
		if (!StringUtil.isEmpty(backFeeEntity.getName())) {
			querySql.append(" AND ocb.loaner_name like ? ");
			pageQuery.addLikeParam(backFeeEntity.getName());
			logger.debug("oa params bill_code={}", backFeeEntity.getBill_code());
		}
		querySql.append(" ORDER BY ocb.bill_code DESC ");
		pageQuery.setQuerySql(querySql.toString());
		return o2OComBackFeeDao.findDataWithPage(pageQuery);
	}
	
	public PageResult<Object[]> findByPageReportList(FormBackFeeEntity backFeeEntity) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, backFeeEntity.getPageSize(), backFeeEntity.getShowO2oPage());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select ocb.bill_code,ocb.loaner_name,ocb.bank_name,ocb.bank_addr,ocb.bank_account,ocb.account_name,ocb.back_risk,ocb.back_manger,ocb.back_deposite, ");
		querySql.append(" case ocb.bill_status when 'NEW' then '新建' when 'RUN' then '审核中' when 'CLO' then '流程结束' end ");
		querySql.append(" from o2o_com_backfee ocb  ");
		querySql.append(" where 1=1 ");
		if (!StringUtil.isEmpty(backFeeEntity.getBill_code())) {
			querySql.append(" AND ocb.bill_code = ? ");
			pageQuery.addParam(backFeeEntity.getBill_code());
			logger.debug("oa params bill_code={}", backFeeEntity.getBill_code());
		}
		if (!StringUtil.isEmpty(backFeeEntity.getName())) {
			querySql.append(" AND ocb.loaner_name like ? ");
			pageQuery.addLikeParam(backFeeEntity.getName());
			logger.debug("oa params bill_code={}", backFeeEntity.getBill_code());
		}
		querySql.append(" ORDER BY ocb.bill_code DESC ");
		pageQuery.setQuerySql(querySql.toString());
		return o2OComBackFeeDao.findDataWithPage(pageQuery);
	}
	
	
	public List<O2OComBackFee> findEntitys(String billcode){
		StringBuffer querySql = new StringBuffer();
		querySql.append("select * from o2o_com_backfee t where t.bill_code = ? ");
		return o2OComBackFeeDao.findEntitys(querySql.toString(), O2OComBackFee.class, billcode);
	}
	
	public List<Object[]> sum(String  billCode){
		List<Object[]> list=null;
		list = o2OComBackFeeDao.findObjectsList("select sum(t.back_risk) ,sum(t.back_manger),sum(t.back_deposite) from o2o_com_backfee t where t.bill_status = 'CLO' and t.bill_code = ? GROUP BY t.bill_code", billCode);
		return list;
	}
	
	/**
	 * 计算出未退的金额，填充到属性中去，不更新表，每次未退都计算出来
	 * @param loanapplyExpand
	 * @return
	 */
	public LoanapplyExpand sumn(LoanapplyExpand loanapplyExpand){  
		//List<Object[]> sum =  sum(loanapplyExpand.getBillCode()); //获取已经退费成功的所有流程信息
		//if(sum!=null&&sum.size()>0){
		if(loanapplyExpand.getRetaDeposite()!=null){
			loanapplyExpand.setRetnDeposite(loanapplyExpand.getRedDeposite().subtract(loanapplyExpand.getRetaDeposite()));
		}else{
			loanapplyExpand.setRetnDeposite(loanapplyExpand.getRedDeposite());
		}
		if(loanapplyExpand.getRetaManage()!=null){
			loanapplyExpand.setRetnManage(loanapplyExpand.getRedManger().subtract(loanapplyExpand.getRetaManage()));
		}else{
			loanapplyExpand.setRetnManage(loanapplyExpand.getRedManger());
		}
		if(loanapplyExpand.getRetaRisk()!=null){
			loanapplyExpand.setRetnRisk(loanapplyExpand.getRedRisk().subtract(loanapplyExpand.getRetaRisk()));
		}else{
			loanapplyExpand.setRetnRisk(loanapplyExpand.getRedRisk());
		}
		//}else{
			/*loanapplyExpand.setRetnDeposite(loanapplyExpand.getRedDeposite());
			loanapplyExpand.setRetnManage(loanapplyExpand.getRedManger());
			loanapplyExpand.setRetnRisk(loanapplyExpand.getRedRisk());*/
		//}
		return loanapplyExpand;
	}
	
	/**
	 * @description 分页查询OA系统管理费、保证金、风险保证金
	 * @param backFeeEntity
	 * @return
	 */
	public PageResult<Object[]> findOaByPage(FormBackFeeEntity backFeeEntity) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, backFeeEntity.getPageSize(), backFeeEntity.getShowOaPage());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT OL.BILLCODE,OLO.LOANERNAME,(case l.credit_status when 0 then 'ZXSX' else l.wf_type end) as 'wf_type' ");
		//querySql.append(" SELECT OL.BILLCODE,OLO.LOANERNAME,OLO.IDCARD,OLO.MOBILE,(OL.CUSTOMERCODE||'-'||OL.CUSTOMERNAME) AS CUSTOMERNAME, ");
		querySql.append(",OL.EXISTDEPOSIT ");
		querySql.append(" ,OL.UNDEPOSIT,(OL.EXISTDEPOSIT-OL.UNDEPOSIT) AS WEIPOSIT,OL.MANAGEFEE,OL.EXISTMANAGEFEE ");
		querySql.append(" ,(OL.MANAGEFEE-OL.EXISTMANAGEFEE) AS WEIMANAGEFEE ,OL.EXISTRISKDEPOSIT, nvl(OL.UNRISKDEPOSIT,0) as UNRISKDEPOSIT ");
		querySql.append(" ,(OL.EXISTRISKDEPOSIT-nvl(OL.UNRISKDEPOSIT,0)) AS WEIUNRISKDEPOSIT,OL.OPERATORNAME ,OL.PAYDATE ");
		querySql.append(" FROM  EADB.O2O_LOANAPPLY OL LEFT JOIN EADB.O2O_LOANER OLO ON OL.LOANAPPLYNO=OLO.LOANAPPLYNO ");
		querySql.append(" WHERE OL.BILLSTATUS = 'CLO' AND OL.ISLOCK = 'N' AND OL.BILLSTATUSINFORM='CLO' ");
		if (!StringUtil.isEmpty(backFeeEntity.getBill_code())) {
			querySql.append(" AND OL.BILLCODE LIKE ? ");
			pageQuery.addLikeParam(backFeeEntity.getBill_code());
			logger.debug("oa params billcode={}", backFeeEntity.getBill_code());
		}
		if (!StringUtil.isEmpty(backFeeEntity.getName())) {
			querySql.append(" AND OLO.LOANERNAME LIKE ? ");
			pageQuery.addLikeParam(backFeeEntity.getName());
		}
		
		if (!StringUtil.isEmpty(backFeeEntity.getPayMentStatus())) {
			if ("O".equals(backFeeEntity.getPayMentStatus())) {
				querySql.append(" and wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(backFeeEntity.getPayMentStatus())){
				querySql.append(" and wf_type=0");
			}else {
				querySql.append(" and wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (backFeeEntity.getPayMentStatus().contains(",")) {
					wfTypeA = backFeeEntity.getPayMentStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + backFeeEntity.getPayMentStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				querySql.append(wfType);
				querySql.append(" ) ");
			}
		}
		
		querySql.append(" ORDER BY OL.BILLCODE DESC ");
		pageQuery.setQuerySql(querySql.toString());
		return oaComBackFeeDao.findDataWithPage(pageQuery);
	}

	// 新系统是否有需要退费的 一。是否是已经交了
	public boolean isHasBackFee(String loanapplyNo) {
		LoanapplyExpand loanapplyExpand = loanapplyExpandService.queryLoanapplyNo(loanapplyNo);
		BigDecimal redManger = loanapplyExpand.getRedManger();
		BigDecimal redDeposite = loanapplyExpand.getRedDeposite();
		BigDecimal redRisk = loanapplyExpand.getRedRisk();
		if ((redManger != null) && (redManger.compareTo(BigDecimal.ZERO) != 0) || (redDeposite != null) && (redDeposite.compareTo(BigDecimal.ZERO) != 0) || (redRisk != null) && (redRisk.compareTo(BigDecimal.ZERO) != 0)) {
			return true;
		} else {
			return false;
		}
	}
	
	// 老系统是否有需要退费的 一。是否是已经交了
	public boolean isHasBackFeeOa(String loanapplyNo) {
		OaBackFeeEntity oaBackFee = findOaBackFeeById(loanapplyNo);
		BigDecimal redManger = oaBackFee.getNotRetreatBond();  //未退管理费
		BigDecimal redDeposite = oaBackFee.getNotRetreatFee();  //未退保证金
		BigDecimal redRisk = oaBackFee.getNotRetreatRisk();	//未退风险补偿金
		if ((redManger != null) && (redManger.compareTo(BigDecimal.ZERO) != 0) || (redDeposite != null) && (redDeposite.compareTo(BigDecimal.ZERO) != 0) || (redRisk != null) && (redRisk.compareTo(BigDecimal.ZERO) != 0)) {
			return true;
		} else {
			return false;
		}
	}
	

	/**
	 * 是否已经有 退费类型流程在RUN过程中
	 * 
	 * @param type
	 *            退费类型
	 * @param loanapplyNo
	 *            贷款单主键
	 * @return
	 */
	public boolean isHasBackFeeFlow(String type, String loanapplyNo) {
		O2OComBackFee o = null;
		if(StringUtil.isNotBlank(type)){
			 o = fingHasBackFeeFlow(type, loanapplyNo);
		}else{
			 o = fingHasBackFeeFlow(loanapplyNo);
		}
		if (o != null) {
			return false;
		} else {
			logger.info("单号为：" + loanapplyNo + "的单已存在，退费类型为：" + type + "的流程在运行中");
			return true;
		}
	}
	
	
	/**
	 * 老系统中是否已经有 退费类型流程在RUN过程中
	 * 
	 * @param loanapplyNo
	 *            贷款单主键
	 * @return false 已经有单  true 没有单
	 */
	public boolean isHasBackFeeFlowInOa(String loanapplyNo) {
		int count = fingHasBackFeeFlowInOa(loanapplyNo);
		if (count>0) {
			logger.info("单号为：" + loanapplyNo + "的单已存在，老系统中的退费流程在运行中");
			return false;
		} else {
			return true;
		}
	}

	/**
	 * 是否还存在没有退费的
	 * 
	 * @param type
	 * @param loanapplyNo
	 * @return
	 */
	@SuppressWarnings("null")
	public boolean isHasrRetn(String type, String loanapplyNo) {
		LoanapplyExpand loanapplyExpand = loanapplyExpandService.queryLoanapplyNo(loanapplyNo);
		if (type.equals(O2OComBackFee.BACK_TYPE_DEPOSITE)) {// 保证金
			BigDecimal retnDeposite = loanapplyExpand.getRetnDeposite();
			if ((retnDeposite == null) && (retnDeposite.compareTo(BigDecimal.ZERO) == 0))
				return false;
			else
				return true;
		} else if (type.equals(O2OComBackFee.BACK_TYPE_RISK)) {// 补偿金
			BigDecimal retnRisk = loanapplyExpand.getRetnRisk();
			;
			if ((retnRisk == null) && (retnRisk.compareTo(BigDecimal.ZERO) == 0))
				return false;
			else
				return true;
		} else { // 管理费
			BigDecimal retnManage = loanapplyExpand.getRetnManage();
			if ((retnManage == null) && (retnManage.compareTo(BigDecimal.ZERO) == 0))
				return false;
			else
				return true;
		}
	}

	public O2OComBackFee fingHasBackFeeFlow(String type, String loanapplyNo) {
		String sql = "SELECT * from o2o_com_backfee   WHERE loanapply_no = ? and back_type =? and bill_status = 'RUN' ";
		return o2OComBackFeeDao.findEntity(sql, O2OComBackFee.class, loanapplyNo, type);
	}
	
	public O2OComBackFee fingHasBackFeeFlow(String loanapplyNo) {
		String sql = "SELECT * from o2o_com_backfee   WHERE loanapply_no = ? and bill_status = 'RUN' ";
		return o2OComBackFeeDao.findEntity(sql, O2OComBackFee.class, loanapplyNo);
	}
	
	public int fingHasBackFeeFlowInOa(String loanapplyNo) {
		String sql_depositfee = "select count(*) from eadb.O2O_BACKDEPOSITFEE where billstatus = 'RUN' and loanapplyno=?  ";
		
		String sql_managefee = "select count(*) from eadb.O2O_BACKMANAGEFEE where billstatus = 'RUN' and loanapplyno=?  ";
		int num_depositfee = oaComBackFeeDao.findInteger(sql_depositfee,loanapplyNo);
		int num_managefee = oaComBackFeeDao.findInteger(sql_managefee,loanapplyNo); 		
				 
		return num_depositfee+num_managefee;
	}

	/**
	 * @description 更新退费数据
	 * @return
	 */
	public int updateOaBackFee(OaUpdateBackFeeEntity oaUpdateBackFeeEntity) {
//		String update_risk = "UPDATE EADB.O2O_LOANAPPLY OL SET OL.UNRISKDEPOSIT=? WHERE OL.BILLCODE = ?";
		int updateCount = 0;
		updateCount = oaComBackFeeDao.updateById(oaUpdateBackFeeEntity);
//		updateCount = +oaComBackFeeDao.updateById(TransEntity.trans(oaUpdateBackFeeEntity));
//		updateCount = +oaComBackFeeDao.execute(update_risk, oaUpdateBackFeeEntity.getUnDeposit(), oaUpdateBackFeeEntity.getBillCode());
		return updateCount;
	}

	public int findOaBackFeeCount(String billCode) {
		String sql_back = "SELECT COUNT(1) FROM  EADB.O2O_LOANAPPLY OL WHERE OL.BILLSTATUSOTF IN ('RUN','CLO') AND OL.BILLCODE = ?";
		String sql_mana = "SELECT COUNT(1) FROM  EADB.O2O_LOANAPPLY OL WHERE OL.BILLSTATUSMANAGEOTF IN ('RUN','CLO') AND OL.BILLCODE = ?";
		String sql_risk = "SELECT COUNT(OL.UNRISKDEPOSIT) FROM EADB.O2O_LOANAPPLY OL WHERE OL.BILLCODE = ?";
		int back_count = oaComBackFeeDao.findInteger(sql_back, billCode);
		int mana_count = oaComBackFeeDao.findInteger(sql_mana, billCode);
		int risk_count = oaComBackFeeDao.findInteger(sql_risk, billCode);
		int re = 0;
		if (back_count > 0) {
			return OaBackFeeStatus.BACK_COUNT;
		}
		if (mana_count > 0) {
			return OaBackFeeStatus.MANA_COUNT;
		}
		if (risk_count > 0) {
			return OaBackFeeStatus.RISK_COUNT;
		}
		return re;
	}

	/**
	 * 更新贷款保证金管理费等信息
	 * 
	 * @param sql
	 *            更新语句
	 * @param billCode
	 *            贷款编号（参数）
	 * @return
	 */
	public int updateBackFee(String sql, String billCode) {
		return oaComBackFeeDao.execute(sql, billCode);
	}

	public int updateBackFee(LoanapplyExpand loanapplyExpand) {
		//更新老系统中的已退保证金，已退管理费，已退风险保证金
		String URL = " OL.UNDEPOSIT=?, OL.EXISTMANAGEFEE=? , UNRISKDEPOSIT=? ";
		String updateSql = "UPDATE EADB.O2O_LOANAPPLY OL SET " + URL + " WHERE OL.BILLCODE=?";
		return oaComBackFeeDao.execute(updateSql, loanapplyExpand.getRetaDeposite(),loanapplyExpand.getRetaManage(),loanapplyExpand.getRetaRisk(),loanapplyExpand.getBillCode());
	}
	
	
	/**
	 * 查询贷款保证金管理费等信息
	 * 
	 * @param sql
	 *            查询语句
	 * @param billCode
	 *            贷款编号（参数）
	 * @return
	 */
	public List<Object[]> findBackFee(String sql, String billCode) {
		return oaComBackFeeDao.findEntitys(sql, Object[].class, billCode);
	}

	/**
	 * 根据贷款单号查询基本信息
	 * 
	 * @param billCode
	 * @return
	 */
	public OaBackFeeEntity findOaBackFee(String billCode) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT OL.LOANAPPLYNO,OL.BILLCODE,(OL.CUSTOMERCODE||'-'||OL.CUSTOMERNAME) AS CUSTOMERNAME,OLO.LOANERNAME ");
		sql.append(" ,OL.LOANAMOUNT,OL.LOANTERM,OL.REPAYMENTWAY,OL.LOANRATE,OL.OPERATORNAME,OL.GUARANTEEWAY ");
		sql.append(" ,OL.DEPOSITRATE*OL.LOANAMOUNT AS DEPOSITRATE ,OL.EXISTDEPOSIT,OL.UNDEPOSIT,(OL.EXISTDEPOSIT-OL.UNDEPOSIT) AS WEIPOSIT ");
		sql.append(" ,OL.MANAGEFEERATE*OL.LOANAMOUNT/12*OL.LOANTERM AS MANAGEFEERATE,OL.MANAGEFEE,OL.EXISTMANAGEFEE,(OL.MANAGEFEE-OL.EXISTMANAGEFEE) AS WEIMANAGEFEE ");
		sql.append(" ,(CASE WHEN OL.LOANTERM<=6 THEN OL.LOANAMOUNT*OL.RISKDEPOSITRATE*1/2 WHEN OL.LOANTERM>6 THEN OL.LOANAMOUNT*OL.RISKDEPOSITRATE END) AS RISKDEPOSITRATE ");
		sql.append(" ,OL.EXISTRISKDEPOSIT,nvl(OL.UNRISKDEPOSIT,0) as UNRISKDEPOSIT,(OL.EXISTRISKDEPOSIT-nvl(OL.UNRISKDEPOSIT,0)) AS WEIUNRISKDEPOSIT ");
		sql.append(" ,HU.UNITCODE||'-'||HU.NAMECN AS NAMECN ");
		sql.append(" ,OL.BANKTYPE,OL.LOAN_TYPE,OL.SUBMITDATE,OLO.IDCARD,OLO.MOBILE,OL.OPERATORNO,OL.LTD ");
		sql.append(" FROM  EADB.O2O_LOANAPPLY OL LEFT JOIN EADB.O2O_LOANER OLO ON OL.LOANAPPLYNO=OLO.LOANAPPLYNO ");
		sql.append(" JOIN EADB.HR_UNITS HU ON  HU.UNITNO = OL.LTD ");
		sql.append(" WHERE OL.BILLSTATUS = 'CLO' AND OL.ISLOCK = 'N' AND OL.BILLSTATUSINFORM='CLO' AND OL.BILLCODE = ? ");
		return oaDetailDao.findEntity(sql.toString(), OaBackFeeEntity.class, billCode);
	}
	
	/**
	 * 老系统打印收款收据数据
	 * 
	 * @param billCode
	 * @return
	 */
	public OaBackFeeEntity findOaBackFeeByOld(String billCode) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT OL.BILLCODE, OLO.LOANERNAME, OL.EXISTDEPOSIT, OL.MANAGEFEE, OL.EXISTRISKDEPOSIT, OL.PAYDATE ");
		sql.append(" FROM EADB.O2O_LOANAPPLY OL LEFT JOIN EADB.O2O_LOANER OLO ON OL.LOANAPPLYNO = OLO.LOANAPPLYNO ");		
		sql.append(" WHERE OL.BILLCODE = ? ");
		return oaComBackFeeDao.findEntity(sql.toString(), OaBackFeeEntity.class, billCode);
	}
	
	/**
	 * 根据贷款单id查询基本信息
	 * 
	 * @param loanapplyno
	 * @return
	 */
	public OaBackFeeEntity findOaBackFeeById(String loanapplyno) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT OL.LOANAPPLYNO,OL.BILLCODE,(OL.CUSTOMERCODE||'-'||OL.CUSTOMERNAME) AS CUSTOMERNAME,OLO.LOANERNAME ");
		sql.append(" ,OL.LOANAMOUNT,OL.LOANTERM,OL.REPAYMENTWAY,OL.LOANRATE,OL.OPERATORNAME,OL.GUARANTEEWAY ");
		sql.append(" ,OL.DEPOSITRATE*OL.LOANAMOUNT AS DEPOSITRATE ,OL.EXISTDEPOSIT,OL.UNDEPOSIT,(OL.EXISTDEPOSIT-OL.UNDEPOSIT) AS WEIPOSIT ");
		sql.append(" ,OL.MANAGEFEERATE*OL.LOANAMOUNT/12*OL.LOANTERM AS MANAGEFEERATE,OL.MANAGEFEE,OL.EXISTMANAGEFEE,(OL.MANAGEFEE-OL.EXISTMANAGEFEE) AS WEIMANAGEFEE ");
		sql.append(" ,(CASE WHEN OL.LOANTERM<=6 THEN OL.LOANAMOUNT*OL.RISKDEPOSITRATE*1/2 WHEN OL.LOANTERM>6 THEN OL.LOANAMOUNT*OL.RISKDEPOSITRATE END) AS RISKDEPOSITRATE ");
		sql.append(" ,OL.EXISTRISKDEPOSIT,nvl(OL.UNRISKDEPOSIT,0) as UNRISKDEPOSIT,(OL.EXISTRISKDEPOSIT-nvl(OL.UNRISKDEPOSIT,0)) AS WEIUNRISKDEPOSIT ");
		sql.append(" ,HU.UNITCODE||'-'||HU.NAMECN AS NAMECN ");
		sql.append(" ,OL.BANKTYPE,OL.LOAN_TYPE,OL.SUBMITDATE,OLO.IDCARD,OLO.MOBILE,OL.OPERATORNO,OL.LTD ");
		sql.append(" FROM  EADB.O2O_LOANAPPLY OL LEFT JOIN EADB.O2O_LOANER OLO ON OL.LOANAPPLYNO=OLO.LOANAPPLYNO ");
		sql.append(" JOIN EADB.HR_UNITS HU ON  HU.UNITNO = OL.LTD ");
		sql.append(" WHERE OL.BILLSTATUS = 'CLO' AND OL.ISLOCK = 'N' AND OL.BILLSTATUSINFORM='CLO' AND OL.LOANAPPLYNO = ? ");
		return oaDetailDao.findEntity(sql.toString(), OaBackFeeEntity.class, loanapplyno);
	}
	
	/**
	 * 根据贷款编号查询缴费信息
	 * @param billCode 贷款编号
	 * @return
	 */
	public OaUpdateBackFeeEntity findOaById(String billCode){
		return oaComBackFeeDao.getById(OaUpdateBackFeeEntity.class, billCode);
	}

	public void save(O2OComBackFee o2OComBackFee) {
		logger.info("o2OComBackFee:" + JSONObject.toJSONString(o2OComBackFee));
		o2OComBackFeeDao.insert(o2OComBackFee);
	}
	
	public int updateById(O2OComBackFee o2OComBackFee) {
		return o2OComBackFeeDao.updateById(o2OComBackFee);
	}
	

	/**
	 * 修改工作流 状态
	 * 
	 * @param status
	 * @param loanapplyNo
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public void updateWorkFlowStatus(String status, String loanapplyNo, String backtype) {
		if (status.equals(StatusContexts.WFTYPE_STATUS_NEW)) { // 新建然后run
			String sql = "update o2o_com_backfee o set o.bill_status=?,o.updator=?,o.update_time=? where o.loanapply_no=? and back_type = ? ";
			o2OComBackFeeDao.execute(sql, status, userUtils.getUpdateOr(), userUtils.getUpdateTime(), loanapplyNo, backtype);
		} else {
			String sql = "update o2o_com_backfee o set o.bill_status=?,o.updator=?,o.update_time=? where o.loanapply_no=? and back_type = ? ";
			o2OComBackFeeDao.execute(sql, status, userUtils.getUpdateOr(), userUtils.getUpdateTime(), loanapplyNo, backtype);
		}
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public void updateWorkFlowStatus(String status, String loanapplyNo) {
		if (status.equals(StatusContexts.WFTYPE_STATUS_NEW)) { // 新建然后run
			String sql = "update o2o_com_backfee o set o.updator=?,o.update_time=? where o.loanapply_no=? and bill_status = ? ";
			o2OComBackFeeDao.execute(sql, userUtils.getUpdateOr(), userUtils.getUpdateTime(), loanapplyNo, status );
		} else {
			String sql = "update o2o_com_backfee o set o.bill_status=?,o.updator=?,o.update_time=? where o.loanapply_no=? and bill_status = ? ";
			o2OComBackFeeDao.execute(sql, status, userUtils.getUpdateOr(), userUtils.getUpdateTime(), loanapplyNo,"NEW");
		}
	}
	/**
	 * 启动退费流程
	 */
	public ModelResult<Boolean> backFee(O2oLoanapplyEntity loan) { // 根据单号去启动流程
		logger.info("贷款编号：[{}],贷款类型：[{}],启动退费流程", loan.getBillCode(),loan.getLoanType());
		
		//O2oLoanapplyEntity loanapply = o2oLoanapplyService.findById(loan.getLoanapplyNo());
		WFRequestParam param = new WFRequestParam();
		param.setReferId(loan.getLoanapplyNo());
		param.setReferCode(loan.getBillCode());
		//param.setReferUrl("http://xxxx.eascs.com/getDetail");
		param.setReferUrl(backfeeUrl + "?billCode=" + loan.getBillCode()+"&type=1");
		param.setSystemModule(ProcessTemplate.SYSTEM_MODULE_O2O);
		if(loan.getLoanType().equals(StatusContexts.LOAN_TYPE_BK) || loan.getLoanType().equals(StatusContexts.LOAN_TYPE_BP) || loan.getLoanType().equals(StatusContexts.LOAN_TYPE_BP_JX) || loan.getLoanType().equals(StatusContexts.LOAN_TYPE_SG) || loan.getLoanType().equals(StatusContexts.LOAN_TYPE_YC) || loan.getLoanType().equals(StatusContexts.LOAN_TYPE_SC)){
			param.setProcessType(String.valueOf(ProcessTemplate.PROECESS_TYPE_REFUND_MGR_FEE));
		}else{
			if(loan.getLoanType().equals(StatusContexts.LOAN_TYPE_FQ)||loan.getLoanType().equals(StatusContexts.LOAN_TYPE_EA)){
				param.setProcessType(String.valueOf(ProcessTemplate.PROECESS_TYPE_REFUND_MGR_FEE_FQ));
			}
		}
		param.setProcessVersion("v1");

		StringBuffer appliedDes = new StringBuffer("借款人:");
		appliedDes.append(o2oLoanapplyService.findLoanUserEntity(loan.getLoanapplyNo(),loan.getLoanId()).getUserFullName());
		/*appliedDes.append("，放款金额:");
		java.text.DecimalFormat df=new java.text.DecimalFormat("0.00"); //格式化，保留两位小数
		BigDecimal loanAmount=new BigDecimal(0);
		if ("EA".equals(loan.getLoanType())||"FQ".equals(loan.getLoanType())) {
			loanAmount = service.findDataByBillCode(loan.getBillCode()).getLoanAmount();
		}else{
			loanAmount = o2oLoanService.queryBillCode(loan.getBillCode()).getLoanAmount();
		}
		appliedDes.append(df.format(loanAmount.doubleValue()));*/
		
		param.setAppliedDes(appliedDes.toString());

		User user = UserHolder.getUser();
		param.setAppliedby(user.getNo());
		param.setAppliedbyName(user.getCname());
		param.setOrgNo(loan.getLtd());
		logger.info("退费WFRequestParam对象：[{}]", param.toString());
		ModelResult<Boolean> modelResult = workFlowService.instance(param);
		return modelResult;
	}
	
	public O2OComBackFee queryById(String id) {
		return o2OComBackFeeDao.getById(O2OComBackFee.class, id);
	}

	public O2OComBackFee queryEntity(String loanapplyNo,String billStatus){
		String depositSql="select * from o2o_com_backfee t where t.loanapply_no=?";
		if(!StringUtil.isBlank(billStatus)){
			depositSql = depositSql +"  and bill_status = '"+billStatus+"' ";
		}
		return o2OComBackFeeDao.findEntity(depositSql, O2OComBackFee.class, loanapplyNo.trim());
	}
	
	public O2OComReceiveFee queryReceiveEntity(String loanapplyNo,String billStatus){
		String depositSql="select * from o2o_loanapply_expand t where t.loanapply_no=?";
		if(StringUtil.isBlank(billStatus)){
			depositSql = depositSql +"  and bill_status = '"+billStatus+"' ";
		}
		return o2OComBackFeeDao.findEntity(depositSql, O2OComReceiveFee.class, loanapplyNo.trim());
	}
	
	public String findString(String sql,String billCode){
		return oaComBackFeeDao.findString(sql, billCode);
	}
	
	public String findO2oString(String sql,String billCode){
		return o2OComBackFeeDao.findString(sql, billCode);
	}
	/**
	 * 校验保证金是否正在退款流程
	 * @param loanapplyNo
	 * @return
	 */
	public O2OComBackFee checkBackStatus(String loanapplyNo){
		return o2OComBackFeeDao.checkBackStatus(loanapplyNo);
	}
	
	/**
	 * 校验保证金是否正在划拨流程
	 * @param billcode
	 * @return
	 */
	public String checkIsHasHB_Deposit(String billcode){ //
		String count= o2OComBackFeeDao.findString(" select count(*) from o2o_loanapply_expand_detail where transfer_num =? and transfer_deposit>0 ", billcode);
		if(!StringUtil.isNotBlank(count)){
			count="0";
		}
		return count;
	}
}
